/*== VSLT_prc_Interval_PageResults ===============================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_PageResponseTimes
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)


		DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), [Total] INT,
			[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
			[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
			[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))

		INSERT INTO @Results 
		([id],[Name],[Total],[Minimum],[Average],[Maximum],[Std Dev])
			SELECT
				c.PageId
				,d.RequestUri
				,COUNT(DISTINCT (c.PageDetailId))
				,CAST(MIN(c.ResponseTime) AS Decimal(15,2))
				,CAST(AVG(c.ResponseTime) AS Decimal(15,2))
				,CAST(MAX(c.ResponseTime) AS Decimal(15,2))
				,CAST(STDEV(c.ResponseTime) AS Decimal(15,2))
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
		
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId
				AND c.Outcome = 0

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND d.RequestUri LIKE @NameFilter
				AND (c.Outcome = 0) 
				AND (a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c.PageId

		UPDATE @Results SET 
			[90Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
				(select top 10 percent ResponseTime 
					from LoadTestPageDetail 
					where LoadTestRunId = @RunId 
					AND PageId=[id]
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					AND 
						(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
						AND
						(EndTime < DATEADD(second, @EndOffset, @StartTime))))
					order by ResponseTime desc) as LTD),
			[95Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
				(select top 5 percent ResponseTime 
					from LoadTestPageDetail 
					where LoadTestRunId = @RunId 
					AND PageId=[id]
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					AND 
						(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
						AND
						(EndTime < DATEADD(second, @EndOffset, @StartTime))))
					order by ResponseTime desc) as LTD),
			[Scenario] = (SELECT ScenarioName FROM TSL_View_PageNamesWithIDs WHERE LoadTestRunId = @RunId AND RequestId = [id]),
			[TestName] = (SELECT TestCaseName FROM TSL_View_PageNamesWithIDs WHERE LoadTestRunId = @RunId AND RequestId = [id])
		FROM @Results AS r

		SELECT TOP (@NumberOfRows) [Id]
			,[Name] AS 'Page Name'
			,[Total] AS Qty
			,[Minimum] AS 'Min'
			,[Average] AS 'Avg'
			,[Maximum] AS 'Max'
			,[Std Dev] AS 'StDev'
			,[90Percent] AS '90%'
			,[95Percent] AS '95%'
			,[Scenario]
			,[TestName] 
		FROM @Results ORDER BY [Id] ASC
	END
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_PageResponseTimes TO PUBLIC
    GO

/*== VSLT_prc_Interval_PageThroughput ============================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_PageThroughput
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		DECLARE @Duration Decimal
		SET @Duration = CASE WHEN (@StartOffset IS NULL) 
			THEN (SELECT RunDuration FROM LoadTestRun WHERE LoadTestRunId = @RunId) 
			ELSE @EndOffset - @StartOffset END


		;WITH Aggs AS
		(
			SELECT
				c.PageId AS [PageId]
				,d.RequestUri AS [Name]
				,COUNT(DISTINCT (c.PageDetailId)) AS [Qty]
				--,CAST(AVG( COUNT(DISTINCT (c.PageDetailId)) / @Duration) AS Decimal(15,2)) AS [Throughput]
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
		
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId
				AND c.Outcome = 0

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND d.RequestUri LIKE @NameFilter
				AND (c.Outcome = 0) 
				AND (a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c.PageId
		)

		SELECT
			[PageId]
			,[Name]
			,[Qty]
			,@Duration
			,CAST([Qty] / @Duration AS Decimal(15,2)) AS [Throughput]
		FROM Aggs
	END
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_PageThroughput TO PUBLIC
    GO

/*== VSLT_prc_Interval_PageThresholdMeasure ======================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_PagePercentMeetingThreshold
		@RunId  INT
		,@ThresholdValue Decimal = 1.0
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25

	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		;WITH Counts AS
		(
			SELECT
				c1.PageId AS [PageId]
				,d.RequestUri AS [Name]
				,COUNT(DISTINCT (c1.PageDetailId)) AS [TotalQty]
				,COUNT(DISTINCT (c2.PageDetailId)) AS [MeetingGoal]
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
		
			INNER JOIN dbo.LoadTestPageDetail AS c1 
				ON a.LoadTestRunId = c1.LoadTestRunId 
				AND a.TestDetailId = c1.TestDetailId
				AND c1.Outcome = 0

			LEFT OUTER JOIN dbo.LoadTestPageDetail AS c2 
				ON a.LoadTestRunId = c2.LoadTestRunId 
				AND c1.PageDetailId = c2.PageDetailId
				AND c2.ResponseTime <= @ThresholdValue

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c1.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c1.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c1.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND d.RequestUri LIKE @NameFilter
				AND (c1.Outcome = 0) 
				AND (a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c1.PageId
		)

		SELECT [PageId], [Name], [TotalQty], [MeetingGoal]
			,CAST(([MeetingGoal] / CAST([TotalQty] AS Decimal)) * 100 AS Decimal(15,0)) AS [Percent Meeting Goal]
		FROM Counts
	END
	GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_PagePercentMeetingThreshold TO PUBLIC
    GO

/*== TSL_prc_PageResults_GroupedByName ===========================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_PageResponseTimes_GroupedByName
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@ExcludeNameFilter nvarchar(255) = 'http%'
		,@NumberOfRows INT = 25
	AS
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([Id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Min] DECIMAL(15,2),[Avg] DECIMAL(15,2), [Max] DECIMAL(15,2), 
				[StDev] DECIMAL(15,2), [90%] DECIMAL(15,2),	[95%] DECIMAL(15,2))
	
			INSERT INTO @Results 
			([Id],[Name],[Total],[Min],[Avg],[Max],[StDev])
			SELECT
				0  -- added to keep symmetry between the tables
				,d.RequestUri
				,COUNT(DISTINCT (c.PageDetailId)) AS [Total]
				,CAST(MIN(c2.ResponseTime) AS Decimal(15,2))
				,CAST(AVG(c2.ResponseTime) AS Decimal(15,2))
				,CAST(MAX(c2.ResponseTime) AS Decimal(15,2))
				,CAST(STDEV(c2.ResponseTime) AS Decimal(15,2))
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId

			LEFT OUTER JOIN LoadTestPageDetail AS c2
				ON c.LoadTestRunId = c2.LoadTestRunId
				AND c.PageDetailId = c2.PageDetailId

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND (@ExcludeNameFilter IS NULL OR d.RequestUri NOT LIKE @ExcludeNameFilter)
				AND d.RequestUri LIKE @NameFilter
				AND (c2.Outcome = 0) 
				AND (c2.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri


			UPDATE @Results SET 
			[90%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 10 percent c.ResponseTime, d.RequestUri 
					FROM LoadTestPageDetail AS c
					INNER JOIN dbo.WebLoadTestRequestMap AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.PageId = d.RequestId
					WHERE c.LoadTestRunId = @RunId
						AND RequestUri = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					ORDER BY ResponseTime DESC
					) as LTD
				)
			,[95%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 5 percent c.ResponseTime, d.RequestUri 
					FROM LoadTestPageDetail AS c
					INNER JOIN dbo.WebLoadTestRequestMap AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.PageId = d.RequestId
					WHERE c.LoadTestRunId = @RunId
						AND RequestUri = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					ORDER BY ResponseTime DESC
					) as LTD
				)
		FROM @Results AS r

		SELECT TOP (@NumberOfRows) [Id],[Name],[Total] AS Qty,[Min],[Avg],[Max],[StDev], [90%], [95%] 
		FROM @Results ORDER BY [Avg] DESC
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_PageResponseTimes_GroupedByName TO PUBLIC 
	GO

/*== VSLT_prc_Page_Multirun_Comparison ===========================================
EXEC	VSLT_prc_Page_Multirun_Comparison
		@RunIds = N'[21],[22],[23],[24]'
		,@ComparisonColumn = 'Percentile90'

NOTE1: @RunIds is a single string with a comma separated list of runs and each run being 
	surrounded by square brackets.
NOTE2: @ComparisonColumn is the name of any of the summary columns from the VSLT_View_PageSummary
	view. If this parameter is not sent, the sproc defaults to the 'Average' column.
---------------------------------------------------------------------------------------------
ScenarioName	TestCaseName	RequestUri					21		22		23		24
Scenario1		TestCase01		http://url.com/page1.aspx	0.17	0.16	0.11	0.11
Scenario1		TestCase02		http://url.com/page2.aspx	0.08	0.04	0.03	0.02
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_Multirun_Comparison
		@RunIds nvarchar(1024)
		,@ComparisonColumn nvarchar(25) = 'Average'
	AS
	BEGIN
		DECLARE @RunIdsStripped nvarchar(1024)
		SET @RunIdsStripped = (SELECT REPLACE(REPLACE(@RunIds, '[', ''), ']', ''))

		DECLARE @SQL nvarchar(max)
		SELECT @SQL = N'
			select * from 
			(
					SELECT 
					ScenarioName
					,TestCaseName
					,LoadTestRunId
					,RequestUri
					,CAST(' + @ComparisonColumn + ' AS Decimal(15,2)) AS ' + @ComparisonColumn + '
					FROM TSL_View_PageSummary
					WHERE LoadTestRunId IN ('+ @RunIdsStripped + ')
			) as SourceTable
			PIVOT
			(
			MAX(' + @ComparisonColumn + ') 
				FOR LoadTestRunID in ('+ @RunIds + ')
			) as PivotTable
			ORDER BY RequestUri ASC'
			-- print @SQL
			-- Execute the generated query
			exec sp_executesql @SQL

			--
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Page_Multirun_Comparison TO PUBLIC 
	GO

/*== VSLT_prc_Page_Throughput_Multirun_Comparison ===========================================
EXEC	VSLT_prc_Page_Multirun_Comparison
		@RunIds = N'[21],[22],[23],[24]'

NOTE1: @RunIds is a single string with a comma separated list of runs and each run being 
	surrounded by square brackets.
---------------------------------------------------------------------------------------------
ScenarioName	TestCaseName	RequestUri					21		22		23		24
Scenario1		TestCase01		http://url.com/page1.aspx	0.17	0.16	0.11	0.11
Scenario1		TestCase02		http://url.com/page2.aspx	0.08	0.04	0.03	0.02
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_Throughput_Multirun_Comparison
		@RunIds nvarchar(1024)
	AS
	BEGIN
		DECLARE @RunIdsStripped nvarchar(1024)
		SET @RunIdsStripped = (SELECT REPLACE(REPLACE(@RunIds, '[', ''), ']', ''))

		DECLARE @SQL nvarchar(max)
		SELECT @SQL = N'
			select * from 
			(
					SELECT 
					ScenarioName
					,TestCaseName
					,LoadTestRunId
					,RequestUri
					,CAST(((CAST(PageCount AS Decimal))/ RunDuration) AS Decimal(15,2)) AS Throughput
					FROM VSLT_View_PageSummary
					WHERE LoadTestRunId IN ('+ @RunIdsStripped + ')
			) as SourceTable
			PIVOT
			(
			MAX(Throughput) 
				FOR LoadTestRunID in ('+ @RunIds + ')
			) as PivotTable
			ORDER BY RequestUri ASC'
			print @SQL
			-- Execute the generated query
			exec sp_executesql @SQL
	END
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Page_Throughput_Multirun_Comparison TO PUBLIC 
	GO


/*== VSLT_prc_Interval_TestResults ===============================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_TestResponseTimes
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
	DECLARE @StartTime DATETIME
	SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), [Total] INT,
			[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
			[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
			[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))

		INSERT INTO @Results 
		([id],[Name], [Total], [Minimum],[Average],[Maximum],[Std Dev], [TestName])
			SELECT
				a.TestCaseId   -- added to keep the table shape identical to the transactionbyinterval sproc
				,c.TestCaseName
				,COUNT(DISTINCT (a.TestDetailId)) AS [Total]
				,CAST(MIN(a.ElapsedTime) AS Decimal(15,2)) AS [Min]
				,CAST(AVG(a.ElapsedTime) AS Decimal(15,2)) AS [Avg]
				,CAST(MAX(a.ElapsedTime) AS Decimal(15,2)) AS [Max]
				,CAST(STDEV(a.ElapsedTime) AS Decimal(15,2)) AS [Std Dev]
				,'-'
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN
				dbo.LoadTestCase AS c
				ON a.LoadTestRunId = c.LoadTestRunId
				AND a.TestCaseId = c.TestCaseId		
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR a.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR a.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND (a.Outcome = 10) 
				AND c.TestCaseName LIKE @NameFilter
				AND (a.InMeasurementInterval = 1) 
			GROUP BY c.TestCaseName, a.TestCaseId

		UPDATE @Results SET 
			[90Percent] = (SELECT CAST(MIN(LTD.ElapsedTime) AS Decimal(15,2)) 
							FROM (select top 10 percent ElapsedTime
									from LoadTestTestDetail 
									where LoadTestRunId = @RunId
									AND TestCaseId = r.Id
									AND (Outcome = 10) 
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
											AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ElapsedTime desc) as LTD),
			[95Percent] = (SELECT CAST(MIN(LTD.ElapsedTime) AS Decimal(15,2)) 
							FROM (select top 5 percent ElapsedTime
									from LoadTestTestDetail 
									where LoadTestRunId = @RunId
									AND TestCaseId = r.Id
									AND (Outcome = 10) 
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
											AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ElapsedTime desc) as LTD),
			[Scenario] = (SELECT ScenarioName FROM TSL_View_TestNamesWithIDs WHERE LoadTestRunId = @RunId AND TestCaseId = [id])
		FROM @Results r
					
		SELECT TOP (@NumberOfRows) [Id]
			,[Name] AS 'Test Name'
			,[Total] AS Qty
			,[Minimum] AS 'Min'
			,[Average] AS 'Avg'
			,[Maximum] AS 'Max'
			,[Std Dev] AS 'StDev'
			,[90Percent] AS '90%'
			,[95Percent] AS '95%'
			,[Scenario]
		FROM @Results ORDER BY [Id] ASC
	END
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_TestResponseTimes TO PUBLIC
    GO

/*== VSLT_prc_Interval_TransactionResults ========================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_TransactionResponseTimes
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
				[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
				[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))
	
			INSERT INTO @Results 
			([id],[Name],[Total],[Minimum],[Average],[Maximum],[Std Dev])
				SELECT
					c.TransactionId
					,d.TransactionName
					,COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
					,CAST(MIN(c.ResponseTime) AS Decimal(15,2)) AS [Min]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Avg]
					,CAST(MAX(c.ResponseTime) AS Decimal(15,2)) AS [Max]
					,CAST(STDEV(c.ResponseTime) AS Decimal(15,2)) AS [Std Dev]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestTransactionDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				INNER JOIN dbo.WebLoadTestTransaction AS d
					ON a.LoadTestRunId = d.LoadTestRunId
					AND c.TransactionId = d.TransactionId
				WHERE 
					a.LoadTestRunId = @RunId
					AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
					AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
					AND d.TransactionName LIKE @NameFilter
					AND (a.InMeasurementInterval = 1) 
				GROUP BY d.TransactionName, c.TransactionId

			UPDATE @Results SET 
			[90Percent] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
							FROM (select top 10 percent ResponseTime
									from LoadTestTransactionDetail 
									where LoadTestRunId = @RunId
									AND TransactionId = r.Id
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
										AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ResponseTime desc) as LTD),
			[95Percent] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
							FROM (select top 5 percent ResponseTime
									from LoadTestTransactionDetail 
									where LoadTestRunId = @RunId
									AND TransactionId = r.Id
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
										AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ResponseTime desc) as LTD),
			[Scenario] = (SELECT ScenarioName FROM TSL_View_TransactionNamesWithIDs WHERE LoadTestRunId = @RunId AND TransactionId = [id]),
			[TestName] = (SELECT TestCaseName FROM TSL_View_TransactionNamesWithIDs WHERE LoadTestRunId = @RunId AND TransactionId = [id])
		FROM @Results AS r
		
		SELECT TOP (@NumberOfRows) [Id]
			,[Name] AS 'Transaction Name'
			,[Total] AS Qty
			,[Minimum] AS 'Min'
			,[Average] AS 'Avg'
			,[Maximum] AS 'Max'
			,[Std Dev] AS 'StDev'
			,[90Percent] AS '90%'
			,[95Percent] AS '95%'
			,[Scenario]
			,[TestName] 
		FROM @Results ORDER BY [Id] ASC
	END
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_TransactionResponseTimes TO PUBLIC
    GO

/*== TSL_prc_TransactionResults_GroupedByName ====================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_TransactionResponseTimes_GroupedByName
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS

		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([Id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Min] DECIMAL(15,2),[Avg] DECIMAL(15,2), [Max] DECIMAL(15,2), 
				[StDev] DECIMAL(15,2), [90%] DECIMAL(15,2),	[95%] DECIMAL(15,2))
	
			INSERT INTO @Results 
			([Id], [Name],[Total],[Min],[Avg],[Max],[StDev])
				SELECT
					0   -- Added to keep the table symmetry with other procs
					,d.TransactionName
					,COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
					,CAST(MIN(c.ResponseTime) AS Decimal(15,2)) AS [Min]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Avg]
					,CAST(MAX(c.ResponseTime) AS Decimal(15,2)) AS [Max]
					,CAST(STDEV(c.ResponseTime) AS Decimal(15,2)) AS [Std Dev]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestTransactionDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				INNER JOIN dbo.WebLoadTestTransaction AS d
					ON a.LoadTestRunId = d.LoadTestRunId
					AND c.TransactionId = d.TransactionId
				WHERE 
					a.LoadTestRunId = @RunId
					AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
					AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
					AND d.TransactionName LIKE @NameFilter
				GROUP BY d.TransactionName

			UPDATE @Results SET 
			[90%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 10 percent c.ResponseTime, d.TransactionName 
					FROM LoadTestTransactionDetail AS c
					INNER JOIN dbo.WebLoadTestTransaction AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.TransactionId = d.TransactionId
					WHERE c.LoadTestRunId = @RunId
						AND TransactionName = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					ORDER BY ResponseTime DESC
					) as LTD
				)
			,[95%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 5 percent c.ResponseTime, d.TransactionName 
					FROM LoadTestTransactionDetail AS c
					INNER JOIN dbo.WebLoadTestTransaction AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.TransactionId = d.TransactionId
					WHERE c.LoadTestRunId = @RunId
						AND TransactionName = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					ORDER BY ResponseTime DESC
					) as LTD
				)
		FROM @Results AS r
		
		SELECT TOP (@NumberOfRows) [Id],[Name],[Total] AS Qty,[Min],[Avg],[Max],[StDev], [90%], [95%] 
		FROM @Results ORDER BY [Name] DESC
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_TransactionResponseTimes_GroupedByName TO PUBLIC
	
	GO

/*== VSLT_prc_Interval_PerfmonCounters ===========================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_PerfmonCounterValuesWithIntervals
		@RunId  INT = 2005
		,@Machine NVARCHAR(255) = NULL
		,@Category NVARCHAR(255) = 'Processor'
		,@Counter NVARCHAR(255) = '% Processor Time'
		,@Instance NVARCHAR(255) = '_Total'
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
	AS
	BEGIN	
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		SELECT
			c.InstanceId AS 'Id'
			,c.InstanceName AS 'Name'
			,CAST(MIN(ComputedValue) AS Decimal(15,2)) AS 'Min'
			,CAST(AVG(ComputedValue) AS Decimal(15,2)) AS 'Avg'
			,CAST(MAX(ComputedValue) AS Decimal(15,2)) AS 'Max'
			,CAST(STDEV(ComputedValue) AS Decimal(15,2)) AS 'StDev'
		FROM [dbo].[LoadTestPerformanceCounterSample] AS a

		INNER JOIN LoadTestRunInterval AS b
			ON a.LoadTestRunId = b.LoadTestRunId
			AND a.TestRunIntervalId = b.TestRunIntervalId

		INNER JOIN [dbo].[MTSL_View_PerfmonInstanceNamesAndIds] AS c
			ON a.LoadTestRunId = c.LoadTestRunId
			AND a.InstanceId = c.InstanceId

		WHERE a.LoadTestRunId = @RunId
		AND (c.CategoryName = @Category AND c.CounterName = @Counter AND c.Instance = @Instance)
		AND (@Machine IS NULL OR c.MachineName LIKE @Machine)
		AND (@StartOffset IS NULL OR IntervalEndTime > DATEADD(second, @StartOffset, @StartTime))
		AND (@EndOffset IS NULL OR IntervalEndTime < DATEADD(second, @EndOffset, @StartTime))
		GROUP BY c.InstanceId, c.InstanceName

	END
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_PerfmonCounterValuesWithIntervals TO PUBLIC
    GO

/*== TSL_prc_UserLoadProfile =====================================================
* Gets the number of users at each sampling interval of the test run. Used for calculating constant 
* load intervals from Step Load Test Runs.
* EXAMPLE:
EXEC  TSL_prc_UserLoadProfile
            @RunId = 9
===============================================================================*/
      --START CODE--
      CREATE PROCEDURE TSL_prc_UserLoadProfile
            @RunId int
      AS
      BEGIN
            DECLARE @StartTime DATETIME
            SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

            SELECT 
                  ComputedValue AS UserCount
                  ,DATEDIFF(second, @StartTime, IntervalEndTime) AS [Interval]
            FROM 
                  LoadTestComputedCounterSample
            WHERE 
                  LoadTestRunId = @RunId
                  AND CategoryName = 'LoadTest:Scenario'
                  AND CounterName = 'User Load'
                  AND InstanceName = '_Total'
            ORDER BY [Interval] ASC


      END
      GO
      --START CODE--
      GRANT EXECUTE ON TSL_prc_UserLoadProfile TO PUBLIC
      GO
